//TAE 2008.09.06 (START
var last_recevied_time	= 0;
//TAE 2008.09.06 (END

function openDB() {
	//Set up the local DB
	var conn = new air.SQLConnection();

	var dbFile = air.File.applicationStorageDirectory.resolvePath("twitch.db");

	try
	{
	    conn.open(dbFile);
		return conn;
		
	}
	catch (error)
	{
		dump('cannot open DB file');
		return;
	}
}

function deleteTimelineTable() {
	var conn = openDB();
	var createStmt = new air.SQLStatement();
	createStmt.sqlConnection = conn;

	var sql = "drop table timeline";

	createStmt.text = sql;

	createStmt.addEventListener(air.SQLEvent.RESULT, deleteTimelineTableResult);
	createStmt.addEventListener(air.SQLErrorEvent.ERROR, deleteTimelineTableError);
	
	createStmt.execute();
	
}

function deleteTimelineTableResult(event)
{
    return;
}

function deleteTimelineTableError(event)
{
    return;
}

function dropTimelineTable() {
	var conn = openDB();
	var createStmt = new air.SQLStatement();
	createStmt.sqlConnection = conn;
	
	var sql = "drop table if exists timeline";

	createStmt.text = sql;

	createStmt.addEventListener(air.SQLEvent.RESULT, dropTimelineTableResult);
	createStmt.addEventListener(air.SQLErrorEvent.ERROR, dropTimelineTableError);
	
	createStmt.execute();
	
}

function dropTimelineTableResult(event)
{
    return;
}

function dropTimelineTableError(event)
{
    return;
}


function createTimelineTable() {
	
	var conn = openDB();

	// Create the friend table
	var createStmt = new air.SQLStatement();
	createStmt.sqlConnection = conn;

	var sql = 
	    "CREATE TABLE IF NOT EXISTS timeline (" + 
	    "    id INTEGER PRIMARY KEY AUTOINCREMENT, " + 
		"    twitter_id INTEGER, " + 
	    "    screen_name TEXT, " + 
		"    real_name TEXT, " + 
	    "    profile_image_url TEXT, " + 
	    "    created_at TEXT, " +
		"    text TEXT, " +
		"    source TEXT, " +
		"    type TEXT, " +
		"    service TEXT, " +
	//TAE 2008.08.20 (START
		"    in_reply_status_id INTEGER," +
	//TAE 2008.08.20 (END
	//TAE 2008.08.20 (start	
		"    created_time	    INTEGER " +
	//TAE 2008.08.20 (end
	    ")";
		
	createStmt.text = sql;

	createStmt.addEventListener(air.SQLEvent.RESULT, createTimelineTableResult);
	createStmt.addEventListener(air.SQLErrorEvent.ERROR, createTimelineTableError);

	createStmt.execute();

}

function createTimelineTableResult(event)
{
    return;
}

function createTimelineTableError(event)
{
    return;
}

function updateTimelineClient() {
	var username = air.EncryptedLocalStore.getItem("username");
	var username_identica = air.EncryptedLocalStore.getItem("username_identica");
	var conn = openDB();
	var selectStmt = new air.SQLStatement();
	var content = '';
	// A SQLConnection named "conn" has been created previously
	selectStmt.sqlConnection = conn;
//TAE 2008.08.20 (START
	//selectStmt.text = "SELECT * FROM "+table_name+" ORDER BY twitter_id DESC";
	selectStmt.text = "SELECT * FROM timeline WHERE type is NOT NULL ORDER BY created_time DESC";
//TAE 2008.08.20 (END

	try
	{ 
	    selectStmt.execute();
	    var result = selectStmt.getResult();
	    var numResults = result.data.length;

	    for (i = 0; i < numResults; i++)
	    {  
	        var row = result.data[i];

			var d = new Date();
			var now = d.getTime();
			var t = Date.parse(row.created_at);
			var diff = now - t;
			diff = parseInt(diff);
			diff = diff/1000;
			diff = Math.round(diff);
			if (diff < 0) {
				diff = "0s";			
			} else if (diff > 3600 && diff < 86400) {
				diff = diff/3600;
				diff = Math.round(diff);
				diff = diff+"h";
			} else if (diff > 60 && diff < 3600) {
				diff = diff/60;
				diff = Math.round(diff);
				diff = diff+"m";
			} else if (diff < 60 && diff > 0) {
				diff = diff+"s";
			} else {
				diff = diff/86400;
				diff = Math.round(diff);
				diff = diff+"d";
			}
			update = row.id;
	        if (i == '0') { var new_id = row.twitter_id; }
			var text = unescape(row.text);
			
			if (row.service == 'twitter') {
				// Replace @ signs with links
				var re = new RegExp(/@([\w\d]|$)+\b/ig);
				var matches = text.match(re);
				if (matches && matches.length) { 
					for (var x = 0; x < matches.length; x++) {
						var name = matches[x].substring(1,matches[x].length);
						var url = 'http://twitter.com/'+name;
						var link = "<a href='#' name="+url+" onclick='return gotolink()'> &#64;"+name+"</a>";
						text = text.replace(matches[x], link);
					}
				}
			} else if (row.service == 'identica') {
				// Replace @ signs with links
				var re = new RegExp(/@([\w\d]|$)+\b/ig);
				var matches = text.match(re);
				if (matches && matches.length) { 
					for (var x = 0; x < matches.length; x++) {
						var name = matches[x].substring(1,matches[x].length);
						var url = 'http://identi.ca/'+name;
						var link = "<a href='#' name="+url+" onclick='return gotolink()'> &#64;"+name+"</a>";
						text = text.replace(matches[x], link);
					}
				}
			}
			
			if (row.service == 'twitter') { 
				var url = "http://twitter.com/"+row.screen_name;
			} else if 	(row.service == 'identica') { 
				var url = "http://identi.ca/"+row.screen_name;
			}
			if (row.type == 'friend' && row.service == 'twitter') {
				content += '<div id="friend_container"><span id="pic" style="background-image: url('+row.profile_image_url+')"><img src="/images/pic-bg.png"/></span><div class="text"><span class="screen_name"><a href="#" name="'+url+'" onclick="return gotolink()">'+row.screen_name+'</a></span> '+text+'</div>';
			} else if (row.type == 'replies' && row.service == 'twitter') {
				content += '<div id="reply_container"><span id="pic" style="background-image: url('+row.profile_image_url+')"><img src="/images/pic_reply-bg.png"/></span><div class="text"><span class="screen_name"><a href="#" name="'+url+'" onclick="return gotolink()">'+row.screen_name+'</a></span> '+text+'</div>';
			//TAE 2008.08.18 (start
			//TAE 2008.09.06 (start
			//} else if (row.type == 'dm') {
			} else if ((row.type == 'dm' || row.type == 'sent') && row.service == 'twitter') {
			//TAE 2008.09.06 (end	
				content += '<div id="dm_container"><span id="pic" style="background-image: url('+row.profile_image_url+')"><img src="/images/pic_reply-bg.png"/></span><div class="text"><span class="screen_name"><a href="#" name="'+url+'" onclick="return gotolink()">'+row.screen_name+'</a></span> '+text+'</div>';
			//TAE 2008.08.18 (end
			} else 	if (row.type == 'friend' && row.service == 'identica') {
				content += '<div id="friend_container"><span id="pic" style="background-image: url('+row.profile_image_url+')"><img src="/images/pic-bg.png"/></span><div class="text"><span class="screen_name"><a href="#" name="'+url+'" onclick="return gotolink()">'+row.screen_name+'</a></span> '+text+'</div>';
			} else if (row.type == 'replies' && row.service == 'identica') {
				content += '<div id="reply_container"><span id="pic" style="background-image: url('+row.profile_image_url+')"><img src="/images/pic_reply-bg.png"/></span><div class="text"><span class="screen_name"><a href="#" name="'+url+'" onclick="return gotolink()">'+row.screen_name+'</a></span> '+text+'</div>';
			} else if ((row.type == 'dm' || row.type == 'sent') && row.service == 'identica') {
				content += '<div id="dm_container"><span id="pic" style="background-image: url('+row.profile_image_url+')"><img src="/images/pic_reply-bg.png"/></span><div class="text"><span class="screen_name"><a href="#" name="'+url+'" onclick="return gotolink()">'+row.screen_name+'</a></span> '+text+'</div>';
			}

		//Tae 2008.08.20 (start	
		if (row.service == 'twitter') {		
			content += '<div class="info"><a herf="#" title="::twitter"><img src="/images/twitter.png" /></a>';
			//TAE 2008.09.06 (start
			//if (row.type != 'dm') {
		} else if (row.service == 'identica') {
			content += '<div class="info"><a herf="#" title="::identi.ca"><img src="/images/identica.png" /></a>';
		}
		
		if (row.service == 'twitter' && (row.type != 'dm' && row.type != 'sent')) {
		//TAE 2008.09.06 (end	
			//content += '<div class="info"> '+row.real_name+'  '+diff+' ago from '+row.source;
			content += '<a href="#" onclick="return gotoStatus(\''+ row.screen_name + '\',' + row.twitter_id + ')"> '+diff+' ago</a> ';
		} else 	if (row.service == 'identica' && (row.type != 'dm' && row.type != 'sent')) {
			content += '<a href="#" onclick="return gotoStatus_identica(\''+ row.screen_name + '\',' + row.twitter_id + ')"> '+diff+' ago</a> ';
		} else {
			content += diff+' ago ';
		}

		//TAE 2008.09.06 (start
		//content += 'from '+row.source;
		content += (row.type == 'sent'? ' ' : 'from ') + row.source;
		//TAE 2008.09.06 (end

		//Tae 2008.08.20 (end

	    //Tae 2008.08.20 (start
			if (row.in_reply_status_id > 0 && row.service == 'twitter') {
				//TAE 2008.09.09 (START --> Issue#12
				//content += '&nbsp;<a href="#" onclick="return gotoStatus(\''+ username + '\',' + row.in_reply_status_id + ')"><span id="reply_id">in reply</span></a>';
				var words = text.split(" ");
				var reply_from = words[0].substring(1);

				content += '&nbsp;<a href="#" onclick="return gotoStatus(\''+ reply_from + '\',' + row.in_reply_status_id + ')"><span id="reply_id">in reply</span></a>';
				//TAE 2008.09.09 (End --> Issue#12
			} else if (row.in_reply_status_id > 0 && row.service == 'identica') {
				var words = text.split(" ");
				var reply_from = words[0].substring(1);

				content += '&nbsp;<a href="#" onclick="return gotoStatus_identica(\''+ reply_from + '\',' + row.in_reply_status_id + ')"><span id="reply_id">in reply</span></a>';
			}
		//Tae 2008.08.20 (end

			if ((row.screen_name != username) && row.service == 'twitter') {
				content += '&nbsp;&nbsp;<a href="#" class ="at" onclick="return replyUsername()" title="::reply"><img src="/images/reply.png" alt="" name="'+row.screen_name+'" id="btn_reply"></a>';
				content += '&nbsp;&nbsp;<a href="#" class ="dir" onclick="return dmUsername()" tilte="::direct msg"><img src="/images/dm.png" alt="" name="'+row.screen_name+'" id="btn_dm"></a>';
			} else if ((row.screen_name != username_identica) && row.service == 'identica') {
				content += '&nbsp;&nbsp;<a href="#" class ="at" onclick="return replyUsername()" title="::reply"><img src="/images/reply.png" alt="" name="'+row.screen_name+'" id="btn_reply"></a>';
			}

			content += '</div></div>';
			//dump(content);
		}

		if (update > '0') { 
			//showNotify();
			return content;
		}


	}
	catch (error)
	{ 	
		air.trace('sql retrieval error');
		return;
	}

}


//TAE 2008.08.23 (start
function setHashTag(text) {

		var re = new RegExp(/#([\w\d]|$)+\b/ig);
		var matches = text.match(re);
		if (matches && matches.length) { 
			
			for (var x = 0; x < matches.length; x++) {
				var name = matches[x].substring(1,matches[x].length);
				
				dump(name);
				
				var url = '<a href="#" name="'+ name +'" onclick="return gotoHashTag()">'+matches[x]+'</a>';
				var re2 = eval('/' + matches[x] + '/g');
				text = text.replace(re2, url);
			}
		}
		
		//dump(text);
		return text;
}
//TAE 2008.08.23 (End
	
function setHashTag_identica(text) {

		var re = new RegExp(/#([\w\d]|$)+\b/ig);
		var matches = text.match(re);
		if (matches && matches.length) { 

			for (var x = 0; x < matches.length; x++) {
				var name = matches[x].substring(1,matches[x].length);

				dump(name);

				var url = '<a href="#" name="'+ name +'" onclick="return gotoHashTag_identica()">'+matches[x]+'</a>';
				var re2 = eval('/' + matches[x] + '/g');
				text = text.replace(re2, url);
			}
		}

		//dump(text);
		return text;
}

//TAE 2008.09.06 (start
function showNotify() {
	var username 	= air.EncryptedLocalStore.getItem("username");
	var table_name 	= username + '_timeline';
	var conn 		= openDB();
	var selectStmt 	= new air.SQLStatement();
	
	// A SQLConnection named "conn" has been created previously
	selectStmt.sqlConnection = conn;
	ssql   = "SELECT type, count(*) as cnt, max(created_time) as last_time FROM "+table_name+" where created_time > " + last_recevied_time;
	ssql  += " and screen_name <> '" + username + "' and type <> 'sent' group by type";
	selectStmt.text = ssql;
	
	try
	{ 
	    selectStmt.execute();
	    var result = selectStmt.getResult();
	    var numResults = result.data.length;
		
		for (i = 0; i < numResults; i++)
	    {
			 var row = result.data[i];
			 if(row.cnt < 4) {
				//Display all
				NotifyMsg(row.type);
			 } else {
				//Display summary
				var title = "<img src = 'sandbox\images\twitter.png' />" + username;
				var msg	  = 'Received ' + row.cnt + ' tweets';
				var icon  = 'icons\icon32.png';
				
				Notifications.add(title, msg, icon);
			 }
		}
	} catch(error)	{ 	
		return;
	}

}

function NotifyMsg(strType) {
	var username = air.EncryptedLocalStore.getItem("username");
	var table_name = username + '_timeline';
	var conn = openDB();
	var selectStmt = new air.SQLStatement();
	
	// A SQLConnection named "conn" has been created previously
	selectStmt.sqlConnection = conn;
	selectStmt.text = "SELECT * FROM "+table_name+" where created_time > " + last_recevied_time + " and type = " + strType + " order by twitter_id";
	
	try
	{ 
	    selectStmt.execute();
	    var result = selectStmt.getResult();
	    var numResults = result.data.length;
		
		for (i = 0; i < numResults; i++)
	    {
			 var row   = result.data[i];
			 var title = "<img src = 'sandbox\images\twitter.png' />" + username;
			 var msg	  = row.text;
			 var icon  = row.profile_image_url;
			
			 Notifications.add(title, msg, icon);
		}
	} 
	catch(error)
	{ 	
		return;
	}
}

//TAE 2008.09.06 (end